{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Help Desk - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "List the Company name and the number of calls for those companies with more than 18 calls.\n",
    "\n",
    "```\n",
    "+------------------+----+\n",
    "| Company_name     | cc |\n",
    "+------------------+----+\n",
    "| Gimmick Inc.     | 22 |\n",
    "| Hamming Services | 19 |\n",
    "| High and Co.     | 20 |\n",
    "+------------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Company_name</th>\n",
       "        <th>cc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Gimmick Inc.</td>\n",
       "        <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hamming Services</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>High and Co.</td>\n",
       "        <td>20</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Gimmick Inc.', 22), ('Hamming Services', 19), ('High and Co.', 20)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Company_name\", COUNT(\"Call_ref\") cc\n",
    "FROM \"Issue\" JOIN \"Caller\" ON (\"Issue\".\"Caller_id\"=\"Caller\".\"Caller_id\")\n",
    "  JOIN \"Customer\" ON (\"Caller\".\"Company_ref\"=\"Customer\".\"Company_ref\")\n",
    "GROUP BY \"Company_name\"\n",
    "HAVING COUNT(\"Call_ref\")>18\n",
    "ORDER BY \"Company_name\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Find the callers who have never made a call. Show first name and last name\n",
    "\n",
    "```\n",
    "+------------+-----------+\n",
    "| first_name | last_name |\n",
    "+------------+-----------+\n",
    "| David      | Jackson   |\n",
    "| Ethan      | Phillips  |\n",
    "+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>first_name</th>\n",
       "        <th>last_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>David</td>\n",
       "        <td>Jackson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ethan</td>\n",
       "        <td>Phillips</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('David', 'Jackson'), ('Ethan', 'Phillips')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Caller\".\"First_name\" first_name, \"Caller\".\"Last_name\" last_name\n",
    "  FROM \"Caller\" LEFT JOIN \"Issue\" ON (\"Caller\".\"Caller_id\"=\"Issue\".\"Caller_id\")\n",
    "    GROUP BY \"Caller\".\"Caller_id\"\n",
    "    HAVING COUNT(\"Issue\".\"Call_ref\")<1\n",
    "    ORDER BY \"Caller\".\"First_name\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5\n",
    "\n",
    "```\n",
    "+--------------------+------------+-----------+----+\n",
    "| Company_name       | first_name | last_name | nc |\n",
    "+--------------------+------------+-----------+----+\n",
    "| Pitiable Shipping  | Ethan      | McConnell |  4 |\n",
    "| Rajab Group        | Emily      | Cooper    |  4 |\n",
    "| Somebody Logistics | Ethan      | Phillips  |  2 |\n",
    "+--------------------+------------+-----------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Company_name</th>\n",
       "        <th>First_name</th>\n",
       "        <th>Last_name</th>\n",
       "        <th>nc</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pitiable Shipping</td>\n",
       "        <td>Ethan</td>\n",
       "        <td>McConnell</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rajab Group</td>\n",
       "        <td>Emily</td>\n",
       "        <td>Cooper</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Somebody Logistics</td>\n",
       "        <td>Ethan</td>\n",
       "        <td>Phillips</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Pitiable Shipping', 'Ethan', 'McConnell', 4),\n",
       " ('Rajab Group', 'Emily', 'Cooper', 4),\n",
       " ('Somebody Logistics', 'Ethan', 'Phillips', 2)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Company_name\", b.\"First_name\", b.\"Last_name\", COUNT(\"Call_ref\") nc\n",
    "FROM \"Issue\" JOIN \"Caller\" a ON (\"Issue\".\"Caller_id\"=a.\"Caller_id\")\n",
    "  JOIN \"Customer\" ON (a.\"Company_ref\"=\"Customer\".\"Company_ref\")\n",
    "    JOIN \"Caller\" b ON (\"Customer\".\"Contact_id\"=b.\"Caller_id\")\n",
    "GROUP BY \"Company_name\", b.\"First_name\", b.\"Last_name\"\n",
    "HAVING COUNT(\"Call_ref\")<5\n",
    "ORDER BY \"Company_name\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "For each shift show the number of staff assigned. Beware that some roles may be NULL and that the same person might have been assigned to multiple roles (The roles are 'Manager', 'Operator', 'Engineer1', 'Engineer2').\n",
    "\n",
    "```\n",
    "+------------+------------+----+\n",
    "| Shift_date | Shift_type | cw |\n",
    "+------------+------------+----+\n",
    "| 2017-08-12 | Early      |  4 |\n",
    "| 2017-08-12 | Late       |  4 |\n",
    "| 2017-08-13 | Early      |  3 |\n",
    "| 2017-08-13 | Late       |  2 |\n",
    "| 2017-08-14 | Early      |  4 |\n",
    "| 2017-08-14 | Late       |  4 |\n",
    "| 2017-08-15 | Early      |  4 |\n",
    "| 2017-08-15 | Late       |  4 |\n",
    "| 2017-08-16 | Early      |  4 |\n",
    "| 2017-08-16 | Late       |  4 |\n",
    "+------------+------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Shift_date</th>\n",
       "        <th>Shift_type</th>\n",
       "        <th>cw</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-12</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-12</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-13</td>\n",
       "        <td>Early</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-13</td>\n",
       "        <td>Late</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-14</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-15</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-15</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16</td>\n",
       "        <td>Early</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-08-16</td>\n",
       "        <td>Late</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2017, 8, 12), 'Early', 4),\n",
       " (datetime.date(2017, 8, 12), 'Late', 4),\n",
       " (datetime.date(2017, 8, 13), 'Early', 3),\n",
       " (datetime.date(2017, 8, 13), 'Late', 2),\n",
       " (datetime.date(2017, 8, 14), 'Early', 4),\n",
       " (datetime.date(2017, 8, 14), 'Late', 4),\n",
       " (datetime.date(2017, 8, 15), 'Early', 4),\n",
       " (datetime.date(2017, 8, 15), 'Late', 4),\n",
       " (datetime.date(2017, 8, 16), 'Early', 4),\n",
       " (datetime.date(2017, 8, 16), 'Late', 4)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT agg.\"Shift_date\", agg.\"Shift_type\", COUNT(agg.p) cw FROM\n",
    "(SELECT DISTINCT a.\"Shift_date\", a.\"Shift_type\", a.p FROM\n",
    "  (SELECT \"Shift_date\", \"Shift_type\", \"Manager\" p FROM \"Shift\" \n",
    "    UNION SELECT \"Shift_date\", \"Shift_type\", \"Operator\" p FROM \"Shift\" \n",
    "    UNION SELECT \"Shift_date\", \"Shift_type\", \"Engineer1\" p FROM \"Shift\" \n",
    "    UNION SELECT \"Shift_date\", \"Shift_type\", \"Engineer2\" p FROM \"Shift\" \n",
    "  ) a\n",
    ") AS agg\n",
    "GROUP BY agg.\"Shift_date\", agg.\"Shift_type\"\n",
    "ORDER BY agg.\"Shift_date\", agg.\"Shift_type\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Caller 'Harry' claims that the operator who took his most recent call was abusive and insulting. Find out who took the call (full name) and when.\n",
    "\n",
    "```\n",
    "+------------+-----------+---------------------+\n",
    "| first_name | last_name | call_date           |\n",
    "+------------+-----------+---------------------+\n",
    "| Emily      | Best      | 2017-08-16 10:25:00 |\n",
    "+------------+-----------+---------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>First_name</th>\n",
       "        <th>Last_name</th>\n",
       "        <th>Call_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Emily</td>\n",
       "        <td>Best</td>\n",
       "        <td>2017-08-16 10:25:00</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Emily', 'Best', datetime.datetime(2017, 8, 16, 10, 25))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Staff\".\"First_name\", \"Staff\".\"Last_name\", \"Issue\".\"Call_date\"\n",
    "FROM \"Caller\" JOIN \"Issue\" ON (\"Caller\".\"Caller_id\"=\"Issue\".\"Caller_id\")\n",
    "  JOIN \"Staff\" ON (\"Issue\".\"Taken_by\"=\"Staff\".\"Staff_code\")\n",
    "WHERE \"Caller\".\"First_name\"='Harry'\n",
    "ORDER BY \"Issue\".\"Call_date\" DESC\n",
    "LIMIT 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
